# -*- coding: UTF-8 -*-

# Copyright (c) 2007 Daniele Favara <nomed@dsslive.org>.
#
# This is free software you can redistribute it and/or modify it
# under the terms of the GNU General Public License as published by the
# Free Software Foundation; either version 2, or (at your option) any
# later version.
#
# This software is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this software; see the file COPYING.  If not, write to the Free
# Software Foundation, Inc., 51 Franilin St, Fifth Floor, Boston, MA



from datetime import datetime
from turbogears.database import mapper, metadata, session, bind_meta_data, create_session

if not metadata.is_bound():
    try:
        bind_meta_data()
    except:
        pass
#session = create_session()

# import some basic SQLAlchemy classes for declaring the data model
# (see http://www.sqlalchemy.org/docs/04/ormtutorial.html)
from sqlalchemy import Table, Column, ForeignKey, UniqueConstraint
from sqlalchemy.orm import relation

# import some datatypes for table columns from SQLAlchemy
# (see http://www.sqlalchemy.org/docs/04/types.html for more)
from sqlalchemy import String, Unicode, Integer, DateTime, Float, UnicodeText, Boolean
from turbogears import identity

visit = Table('visit', metadata,
    Column('visit_key', String(40), primary_key=True),
    Column('created', DateTime, nullable=False, default=datetime.now),
    Column('expiry', DateTime)
)

visit_identity = Table('visit_identity', metadata,
    Column('visit_key', String(40), primary_key=True),
    Column('user_id', Integer, ForeignKey('user.user_id'), index=True)
)

group = Table('group', metadata,
    Column('group_id', Integer, primary_key=True),
    Column('group_name', Unicode(16), unique=True),
    Column('display_name', Unicode(255)),
    Column('created', DateTime, default=datetime.now)
)

user = Table('user', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('user_name', Unicode(16), unique=True, nullable=False),
    Column('email_address', Unicode(255), unique=True, nullable=False),
    Column('display_name', Unicode(255)),
    Column('password', Unicode(40)),
    Column('created', DateTime, default=datetime.now)
)

biller = Table('biller', metadata,
    Column('biller_id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey('user.user_id',
        onupdate='CASCADE', ondelete='CASCADE'), unique=True),
    Column('company_name', Unicode(255)),
    Column('company_slogan', Unicode(255)),
    Column('web', Unicode(255)),
    Column('vat_code', Unicode(255), nullable=False),
    Column('fiscal_code', Unicode(255), nullable=False),
    Column('street', Unicode(255)),
    Column('post_code', Integer),
    Column('city', Unicode(255)),
    Column('state_province', Unicode(255)),
    Column('country', Unicode(255)),
    Column('phone', Unicode(255)),
    Column('fax', Unicode(255)),
    Column('created', DateTime, default=datetime.now),
)

customer = Table('customer', metadata,
    Column('customer_id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey('user.user_id',
        onupdate='CASCADE', ondelete='CASCADE')),
    Column('company_name', Unicode(255)),
    Column('company_description', Unicode(255)),
    Column('web', Unicode(255)),
    Column('vat_code', Unicode(255), nullable=False),
    Column('fiscal_code', Unicode(255), nullable=False),
    Column('street', Unicode(255)),
    Column('post_code', Integer),
    Column('city', Unicode(255)),
    Column('state_province', Unicode(255)),
    Column('country', Unicode(255)),
    Column('phone', Unicode(255)),
    Column('fax', Unicode(255)),
    Column('created', DateTime, default=datetime.now),
    UniqueConstraint('user_id', 'vat_code', 'fiscal_code', name='uix')
)

project = Table('project', metadata,
    Column('project_id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey('user.user_id'), nullable=False),
    Column('customer_id', Integer, ForeignKey('customer.customer_id')),
    Column('name', Unicode(16), nullable=False),
    Column('description', Unicode(255)),
    Column('created', DateTime, default=datetime.now),
    UniqueConstraint('user_id', 'customer_id', 'name', name='uix')
    )


task = Table('task', metadata,
    Column('task_id', Integer, primary_key=True),
    Column('title', Unicode(255), nullable=False),
    Column('description', UnicodeText()),
    Column('qty', Float, nullable=False, default=0),
    Column('created', DateTime, default=datetime.now),
    Column('enddate', DateTime),
    Column('begindate', DateTime),
    Column('priority', Unicode(255)),
    Column('active', Boolean, default=True)
)

#project_task = Table('project_task', metadata,
#    Column('project_id', Integer, ForeignKey('project.project_id',
#        onupdate='CASCADE', ondelete='CASCADE')),
#    Column('task_id', Integer, ForeignKey('task.task_id',
#        onupdate='CASCADE', ondelete='CASCADE')),
#
#)

milestone = Table('milestone', metadata,
    Column('milestone_id', Integer, primary_key=True),
    Column('project_id', Integer, ForeignKey('project.project_id',
        onupdate='CASCADE', ondelete='CASCADE')),
    Column('name', Unicode(16),nullable=False),
    Column('description', UnicodeText()),
    Column('created', DateTime, default=datetime.now),
    Column('enddate', DateTime),
    Column('begindate', DateTime),
    UniqueConstraint('milestone_id', 'project_id', 'name', name='uix')
    )

milestone_task = Table('milestone_task', metadata,
    Column('milestone_id', Integer, ForeignKey('milestone.milestone_id',
        onupdate='CASCADE', ondelete='CASCADE')),
    Column('task_id', Integer, ForeignKey('task.task_id',
        onupdate='CASCADE', ondelete='CASCADE')),

)



invoice = Table('invoice', metadata,
    Column('invoice_id', Integer, primary_key=True),
    Column('biller_id', Integer, ForeignKey('biller.biller_id'), nullable=False),
    Column('customer_id', Integer, ForeignKey('customer.customer_id'), nullable=False),
    Column('currency', Unicode(16)),
    Column('created', DateTime, default=datetime.now),
    Column('number', Unicode(255), nullable=False),
    Column('paied', Boolean, default=False, nullable=False),
    Column('tmpl', Unicode(16)),
    Column('emitted', DateTime)
    #Column('tot', Float, nullable=False, default=0 ),
    #Column('gross', Float, nullable=False, default=0 ),
)

proforma = Table('proforma', metadata,
    Column('invoice_id', Integer, primary_key=True),
    Column('biller_id', Integer, ForeignKey('biller.biller_id'), nullable=False),
    Column('customer_id', Integer, ForeignKey('customer.customer_id'), nullable=False),
    Column('currency', Unicode(16)),
    Column('created', DateTime, default=datetime.now),
    Column('number', Unicode(255), nullable=False),
    Column('paied', Boolean, default=False, nullable=False),
    Column('tmpl', Unicode(16)),
    Column('emitted', DateTime)
    #Column('tot', Float, nullable=False, default=0 ),
    #Column('gross', Float, nullable=False, default=0 ),
)

product = Table('product', metadata,
    Column('product_id', Integer, primary_key=True),
    Column('description', Unicode(255)),
    Column('note', Unicode(255)),
    Column('unit_price', Float, nullable=False, default=0 ),
    Column('tot_price', Float, nullable=False, default=0 ),
    Column('gross_price', Float, nullable=False, default=0 ),
    Column('vat', Float, nullable=False, default=0),
    Column('qty', Float, nullable=False, default=0),
    Column('created', DateTime, default=datetime.now),
)

tmp_product = Table('tmp_product', metadata,
    Column('product_id', Integer, primary_key=True),
    Column('description', Unicode(255)),
    Column('note', Unicode(255)),
    Column('unit_price', Float, nullable=False, default=0 ),
    Column('tot_price', Float, nullable=False, default=0 ),
    Column('gross_price', Float, nullable=False, default=0 ),
    Column('vat', Float, nullable=False, default=0),
    Column('qty', Float, nullable=False, default=0),
    Column('created', DateTime, default=datetime.now),
)

invoice_product = Table('invoice_product', metadata,
    Column('invoice_id', Integer, ForeignKey('invoice.invoice_id',
        onupdate='CASCADE', ondelete='CASCADE')),
    Column('product_id', Integer, ForeignKey('product.product_id',
        onupdate='CASCADE', ondelete='CASCADE')),
)

proforma_product = Table('proforma_product', metadata,
    Column('proforma_id', Integer, ForeignKey('proforma.invoice_id',
        onupdate='CASCADE', ondelete='CASCADE')),
    Column('product_id', Integer, ForeignKey('product.product_id',
        onupdate='CASCADE', ondelete='CASCADE')),
)

permission = Table('permission', metadata,
    Column('permission_id', Integer, primary_key=True),
    Column('permission_name', Unicode(16), unique=True),
    Column('description', Unicode(255))
)

user_group = Table('user_group', metadata,
    Column('user_id', Integer, ForeignKey('user.user_id',
        onupdate='CASCADE', ondelete='CASCADE')),
    Column('group_id', Integer, ForeignKey('group.group_id',
        onupdate='CASCADE', ondelete='CASCADE'))
)

group_permission = Table('group_permission', metadata,
    Column('group_id', Integer, ForeignKey('group.group_id',
        onupdate='CASCADE', ondelete='CASCADE')),
    Column('permission_id', Integer, ForeignKey('permission.permission_id',
        onupdate='CASCADE', ondelete='CASCADE'))
)


# the identity model


class Visit(object):
    """
    A visit to your site
    """
    def lookup_visit(cls, visit_key):
        try:
            return session.query(cls).filter_by(visit_key=visit_key).first()
        except:
            pass
    lookup_visit = classmethod(lookup_visit)



class VisitIdentity(object):
    """
    A Visit that is link to a User object
    """
    def __init__(self, visit_key=None, user_id=None):
        self.visit_key = visit_key
        self.user_id = user_id



class Group(object):
    """
    An ultra-simple group definition.
    """
    pass


class User(object):
    """
    Reasonably basic User definition.
    Probably would want additional attributes.
    """
    def permissions(self):
        perms = set()
        for g in self.groups:
            perms |= set(g.permissions)
        return perms
    permissions = property(permissions)

    def by_email_address(cls, email):
        """
        A class method that can be used to search users
        based on their email addresses since it is unique.
        """
        return cls.query.filter_by(email_address=email).first()

    by_email_address = classmethod(by_email_address)

    def by_user_name(cls, username):
        """
        A class method that permits to search users
        based on their user_name attribute.
        """
        return cls.query.filter_by(user_name=username).first()

    by_user_name = classmethod(by_user_name)

    def _set_password(self, password):
        """
        encrypts password on the fly using the encryption
        algo defined in the configuration
        """
        self._password = identity.encrypt_password(password)

    def _get_password(self):
        """
        returns password
        """
        return self._password

    password = property(_get_password, _set_password)


class Permission(object):
    """
    A relationship that determines what each Group can do
    """
    pass

class Biller(object):
    """

    """

    def _get_id(self):
        return self.biller.user_id

    id = property(_get_id)

class Customer(object):
    """
    """
    pass

class Project(object):
    pass

class Milestone(object):
    pass

class Task(object):
    pass

class Invoice(object):

    def _get_tot(self):
        ret = float(0)
        for product in  self.products:
            ret += product.tot_price
        return ret

    tot = property(_get_tot)

    def _get_gross(self):
        ret = float(0)
        for product in  self.products:
            ret += product.gross_price
        return ret

    gross = property(_get_gross)

    def _get_date_now(self):
        t = self.created
        year =  t.strftime("%Y")
        month = t.strftime("%m")
        day = t.strftime("%d")
        return year, month,day

    date = property(_get_date_now)

class Proforma(Invoice):
    pass

class Product(object):
    pass


class TmpProduct(object):
    pass

# set up mappers between identity tables and classes

mapper(Visit, visit)

mapper(VisitIdentity, visit_identity,
        properties=dict(users=relation(User, backref='visit_identity')))

mapper(User, user,
       properties={
                   '_password' : user.c.password,
                   'biller': relation(
                        Biller, backref="user_billers"),
                   'customer': relation(
                        Customer, backref="user_customers"),
                  })
mapper(Biller, biller,
       properties={
                   'user':relation(
                        User, backref='biller_users') ,
                  })
mapper(Customer, customer,
       properties={
                   'user':relation(
                        User, backref='customer_users') ,
                  })

mapper(Project, project,
       properties={
                   'user':relation(
                        User, backref='project_user') ,
                   'customer':relation(
                        Customer, backref='project_customer'),
                   #'tasks': relation(
                   #     Task, secondary=project_task,
                   #     backref='project_tasks'),
                   'milestones': relation(
                        Milestone, backref='project_milestone')
                  })

mapper(Task, task,
        properties={
                    #'project': relation(
                    #    Project, secondary=project_task,
                    #    backref='task_project'),
                    'milestone': relation(
                        Milestone, secondary=milestone_task,
                        backref='milestone_task')
                })

mapper(Milestone, milestone,
        properties={
                    'tasks': relation(
                        Task, secondary=milestone_task,
                        backref='milestone_tasks'),
                    'project': relation(
                        Project, backref='milestone_project')
                })

mapper(Invoice, invoice,
       properties={
                   'biller':relation(
                        Biller, backref='invoice_biller') ,
                   'customer':relation(
                        Customer, backref='invoice_customer'),
                   'products': relation(
                        Product, secondary=invoice_product,
                        backref='invoice_products')
                  })

mapper(Proforma, proforma,
       properties={
                   'biller':relation(
                        Biller, backref='proforma_biller') ,
                   'customer':relation(
                        Customer, backref='proforma_customer'),
                   'products': relation(
                        Product, secondary=proforma_product,
                        backref='proforma_products')
                  })

mapper(Product, product,
        properties={
                    'invoice': relation(
                        Invoice, secondary=invoice_product,
                        backref='product_invoice'),
                    'proforma': relation(
                        Proforma, secondary=proforma_product,
                        backref='product_proforma'),
                })

#mapper(TmpProduct, tmp_product,
#        properties={
#                    'proforma': relation(
#                        Proforma, secondary=proforma_product,
#                        backref='product_proforma'),
#                })

mapper(Group, group,
        properties=dict(users=relation(User,
                secondary=user_group, backref='groups')))

mapper(Permission, permission,
        properties=dict(groups=relation(Group,
                secondary=group_permission, backref='permissions')))
